package com.joysuch.wwyt.risk.repository;

import com.joysuch.wwyt.risk.entity.RiskPointEvaluationDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface RiskPointEvaluationDetailDao extends JpaRepository<RiskPointEvaluationDetail, Long> {
    List<RiskPointEvaluationDetail> findByItemId(Long id);

    @Query(value = "SELECT * FROM wwyt_risk_point_business_evaluation_detail WHERE find_in_set(:controlLevelId , control_level_ids)", nativeQuery = true)
    List<RiskPointEvaluationDetail> findByControlLevelId(Long controlLevelId);

    @Query(value = "SELECT * FROM wwyt_risk_point_business_evaluation_detail WHERE point_evaluation_id =?1 AND delete_flag = '0'", nativeQuery = true)
    List<RiskPointEvaluationDetail> findByPointEvaluationId(Long pointEvaluationId);

    @Query(value = "SELECT id FROM wwyt_risk_point_business_evaluation_detail WHERE point_evaluation_id =?1 AND delete_flag = '0'", nativeQuery = true)
    List<Long> findIdsByPointEvaluationId(Long pointEvaluationId);

    @Query(value = "select * from wwyt_risk_point_business_evaluation_detail " +
            "where delete_flag = '0' and method_analyze_type = 2 and control_state in :controlStateList and point_evaluation_id in :collect and method_id is not null and content is not null " +
            "and if(:controlLevelId != '', find_in_set(:controlLevelId, control_level_ids),1=1) " +
            "and if(:responseDepartId != '', find_in_set(:responseDepartId , response_depart_ids),1=1)", nativeQuery = true)
    List<RiskPointEvaluationDetail> pageControlPointDetail(@Param("collect") List<Long> collect,
                                                           @Param("controlLevelId") Long controlLevelId,
                                                           @Param("responseDepartId") Long responseDepartId,
                                                           @Param("controlStateList") List<Integer> controlStateList);

    @Modifying
    @Query(value = "update `wwyt_risk_point_business_evaluation_detail` set delete_flag = '1' where item_id in ?1", nativeQuery = true)
    void deleteByItemIds(List<Long> deleteDetailIds);

    @Modifying
    @Query(value = "update `wwyt_risk_point_business_evaluation_detail` set delete_flag = '1' where id in ?1", nativeQuery = true)
    void deleteByIds(List<Long> deleteDetailIds);

    @Query(value = "select count(1) " +
            "            from wwyt_risk_point_business_evaluation_detail " +
            "            where point_evaluation_id in ?2 " +
            "              and method_analyze_type = 2 " +
            "              and method_id = ?1 and delete_flag = '0' ", nativeQuery = true)
    int countByMethodId(Long typeId,List<Long> lastEvaluationIds);

    @Query(value = "select * from `wwyt_risk_point_business_evaluation_detail` where item_id in ?1", nativeQuery = true)
    List<RiskPointEvaluationDetail> findByItemIds(List<Long> deleteDetailIds);

    @Query(value = "select * from `wwyt_risk_point_business_evaluation_detail` where id in ?1", nativeQuery = true)
    List<RiskPointEvaluationDetail> findByDetailIds(List<Long> deleteDetailIds);

    @Query(value = "select control_level_ids " +
            "from  " +
            "     wwyt_risk_point_business_evaluation_detail detail inner join  " +
            "     (SELECT max(temp2.id) id, temp2.tenent_id TENENT_ID, temp2.org_code org_code " +
            "      FROM (SELECT eva.id, eva.point_id, eva.tenent_id tenent_id, eva.org_code org_code " +
            "            FROM (SELECT point_id, max(evaluation_date) AS max_evaluation_date " +
            "                  FROM wwyt_risk_point_business_evaluation " +
            "                  where point_id = ?1 and delete_flag = '0'" +
            "                  GROUP BY point_id) temp " +
            "                     INNER JOIN wwyt_risk_point_business_evaluation eva ON eva.point_id = temp.point_id AND " +
            "                                                                           temp.max_evaluation_date = " +
            "                                                                           eva.evaluation_date and eva.delete_flag = '0') temp2 " +
            "      GROUP BY temp2.point_id, temp2.tenent_id, temp2.org_code) evaluationIds " +
            "                    on evaluationIds.id = detail.point_evaluation_id and detail.method_analyze_type = 2 and detail.delete_flag = '0'", nativeQuery = true)
    List<String> findControlLevelByPointId(Long pointId);

    @Query(value = "select plan.executor " +
                    "from wwyt_risk_point_business_evaluation_detail detail " +
                    "         inner join " +
                    "     ( " +
                    "         SELECT max(temp2.id) id, temp2.tenent_id TENENT_ID, temp2.org_code org_code " +
                    "         FROM ( " +
                    "                  SELECT eva.id, " +
                    "                         eva.tenent_id tenent_id, " +
                    "                         eva.point_id, " +
                    "                         eva.org_code  org_code " +
                    "                  FROM (SELECT point_id, max(evaluation_date) AS max_evaluation_date " +
                    "                        FROM wwyt_risk_point_business_evaluation " +
                    "                        where point_id = ?1 and delete_flag = '0'" +
                    "                        GROUP BY point_id) temp " +
                    "                           INNER JOIN wwyt_risk_point_business_evaluation eva " +
                    "                                      ON eva.point_id = temp.point_id " +
                    "                                          AND temp.max_evaluation_date = eva.evaluation_date eva.delete_flag = '0'" +
                    "              ) temp2 " +
                    "         GROUP BY temp2.point_id, temp2.tenent_id, temp2.org_code) evaluationIds " +
            "     on evaluationIds.id = detail.point_evaluation_id and detail.method_analyze_type = 2 and detail.delete_flag = '0'" +
            "         inner join wwyt_risk_hidden_danger_content hidden on " +
            "    detail.id = hidden.detail_id and hidden.delete_flag = '0'" +
            "         inner join wwyt_bp_investigate_plan plan on " +
            "    hidden.id = plan.hidden_danger_content_id and plan.delete_flag = '0'", nativeQuery = true)
    List<String> findResPersonsByPointIdForNew(Long id);


    @Query(value = "SELECT * FROM `wwyt_risk_point_business_evaluation_detail` WHERE point_evaluation_id =?1 AND method_analyze_type = ?2 AND delete_flag = '0';", nativeQuery = true)
    List<RiskPointEvaluationDetail> findByPointEvaluationIdAndMethodType(Long stableEvaluationId, int i);

    @Query(value = "select * from wwyt_risk_point_business_evaluation_detail where response_depart_ids like %?1% and DELETE_FLAG = '0'", nativeQuery = true)
    List<RiskPointEvaluationDetail> findByDepartId(Long id);

}
